We have covered this briefly before, but its an important enough concept to cover again in more detail.
Problem: You are running out of disk space on the drive you keep PostgreSQL data on
Solution:
Create a new tablespace on a separate drive and move existing tables to it, or create a new tablespace and use for future tables.
What is a tablespace and how to create a tablespace
A tablespace in PostgreSQL is similar to a tablespace in Oracle and a filegroup in SQL Server. It segments a piece of physical disk space
for use by the PostgreSQL process for holding data. Below are steps to creating a new tablespace. Tablespaces have existed since PostgreSQL 8.0.
More about tablespaces in PostgreSQL is outlined in
the manual PostgreSQL 8.3 tablespaces
While it is possible to create a table index on a different tablespace from the table, we won't be covering that.
Below are steps to creating one
- First create a folder on an available disk in your filesystem using an filesystem server administrative login
- Next give full rights to the postgres server account (the one the daemon process runs under) or you can change the owner of the folder
to the postgres account (in linux you can use chown postgres and on windows just use the administrative properties panel for that folder.
- Then launch psql or pgadmin III and connect as a super user
- At query window type:
For Linux you will have something like this
CREATE TABLESPACE mynewtablespace LOCATION /path/to/mynewfolder
For windows you will have something like this:
CREATE TABLESPACE mynewtablespace LOCATION 'D:/mynewfolder';
Moving existing tables to the new tablespace
You move existing tables with the ALTER TABLE command. You can do the below replacing the my... variables with your specific one. mytableschema if
you are not using schemas for logical partitioning would simply be public.
- Again connect via psql or PgAdmin III
- ALTER TABLE mytableschema.mytable SET TABLESPACE mynewtablespace
Setting default location for new tables
As of PostgreSQL 8.2, PostgreSQL provides many options for setting the default location of newly created tables
- You can default location based on the database using:
ALTER DATABASE mydatabase SET default_tablespace = mynewtablespace
If you happened to have used mixed case when creating your database, you will need to quote the database name e.g
ALTER DATABASE "My_Database" SET default_tablespace = mynewtablespace;
- based on the user creating the table.
ALTER ROLE someuser SET default_tablespace = mynewtablespace;
- Or temporarily for current session while you are creating a batch of tables using
SET default_tablespace = mynewtablespace;
PostGIS in Action
About the Authors
Consulting
